﻿using System;
using System.Collections.Generic;
using System.Text;
using CM = ZofX.NpoiXHelper.Model;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.HSSF.Util;
using NPOI.SS.Util;
using System.Reflection;
using System.Collections;
using System.Web;
using System.Data;
using System.Text.RegularExpressions;

namespace ZofX.NpoiXHelper
{
    /// <summary>
    /// Excel导出类
    /// </summary>
    public class XlsExporter
    {
        /// <summary>
        /// 
        /// </summary>
        private static Regex regexIndexProperty = new Regex(@"(.+)\[(\d+)\]");

        /// <summary>
        /// 导出List到Excel
        /// </summary>
        /// <param name="Code">配置编码</param>
        /// <param name="List">DataTable数据源</param>
        /// <param name="PageSize">每个Sheet的记录数</param>
        /// <param name="Response">HTTP响应对象</param>
        public static void Export(string Code, IList List, int PageSize, HttpResponse Response)
        {
            //===================获取配置=========================
            CM.Table table = CM.Config.GetXlsExporterConfig(Code);
            table.Columns.Sort((c1, c2) =>
            {
                return c1.Order - c2.Order;
            });
            string strXlsName = table.Name + "-" + DateTime.Now.ToString("yyMMddHHmmss");

            int intMaxPage = 1;
            if (PageSize > 0)
            {
                intMaxPage = (0 == List.Count % PageSize) ? (List.Count / PageSize) : (List.Count / PageSize + 1);
            }
            //====================================================

            //===================创建表格=========================
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "ZofX";
            hssfworkbook.DocumentSummaryInformation = dsi;
            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = strXlsName;
            hssfworkbook.SummaryInformation = si;

            int intNowPage = 1, intIndex = 0;
            for (int intPage = intNowPage; intPage <= intMaxPage; intPage++)
            {
                //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
                //So we insert three sheet just like what Excel does
                ISheet sheet1 = hssfworkbook.CreateSheet(table.Name + "_" + intPage);
                ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false;
                ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false;
                //====================================================

                //===================创建标题=========================
                IRow row = sheet1.CreateRow(0);
                row.Height = 500;
                ICell cell = row.CreateCell(0, CellType.String);
                cell.SetCellValue(table.Name);
                ICellStyle style = hssfworkbook.CreateCellStyle();
                style.VerticalAlignment = VerticalAlignment.Center;
                style.Alignment = HorizontalAlignment.Center;
                IFont font = hssfworkbook.CreateFont();
                font.Color = HSSFColor.Black.Index;
                font.FontHeight = 250;
                font.Boldweight = 1000;
                style.SetFont(font);
                cell.CellStyle = style;
                sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
                //====================================================

                //===================创建表头=========================
                row = sheet1.CreateRow(1);
                row.Height = 400;
                int rowIndex = 0;
                foreach (CM.Column c in table.Columns)
                {
                    cell = row.CreateCell(rowIndex, CellType.String);
                    cell.SetCellValue(c.Name);

                    style = hssfworkbook.CreateCellStyle();
                    style.BorderBottom = BorderStyle.Thin;
                    style.BottomBorderColor = HSSFColor.Black.Index;
                    style.BorderLeft = BorderStyle.Thin;
                    style.LeftBorderColor = HSSFColor.Black.Index;
                    style.BorderRight = BorderStyle.Thin;
                    style.RightBorderColor = HSSFColor.Black.Index;
                    style.BorderTop = BorderStyle.Thin;
                    style.TopBorderColor = HSSFColor.Black.Index;
                    style.VerticalAlignment = VerticalAlignment.Center;
                    font = hssfworkbook.CreateFont();
                    font.Color = HSSFColor.Black.Index;
                    font.FontHeight = 200;
                    font.Boldweight = 1000;
                    style.SetFont(font);
                    cell.CellStyle = style;

                    sheet1.SetColumnWidth(rowIndex++, 5000);
                }
                //====================================================

                //===================写入数据=========================
                rowIndex = 0;
                if (null != List)
                {
                    //foreach (object obj in list)
                    for (; intIndex < List.Count && intIndex < PageSize * intPage; intIndex++)
                    {                                        
                        row = sheet1.CreateRow(2 + rowIndex++);
                        row.Height = 400;

                        int colIndex = 0;
                        foreach (CM.Column c in table.Columns)
                        {
                            object val = List[intIndex];
                            Type type = val.GetType();
                            string[] property = c.Property.Split('.');
                            PropertyInfo pInfo = null;
                            foreach (string p in property)
                            {
                                pInfo = type.GetProperty(p);
                                if (null == pInfo) break;

                                val = pInfo.GetValue(val, null);
                                if (null == val) break;

                                type = val.GetType();
                                if (null == type) break;
                            }

                            cell = row.CreateCell(colIndex, CellType.String);
                            if (null != val)
                            {
                                if (val is DateTime)
                                {
                                    cell.SetCellValue(((DateTime)val).ToString("yyyy-MM-dd HH:mm:ss"));
                                }
                                else
                                {
                                    cell.SetCellValue(val.ToString());
                                }
                            }
                            else
                            {
                                cell.SetCellValue("");
                            }
                            style.GetFont(hssfworkbook).Boldweight = 0;
                            cell.CellStyle = style;
                            sheet1.SetColumnWidth(colIndex++, 5000);
                        }
                    }
                }
                //====================================================
            }

            if (null != Response)
            {
                Response.ClearHeaders();
                Response.ContentType = "application/vnd.ms-excel";
                Response.Charset = "UTF-8";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", strXlsName + ".xls"));
                Response.Clear();

                MemoryStream file = new MemoryStream();
                hssfworkbook.Write(file);
                Response.BinaryWrite(file.GetBuffer());

                Response.Flush();
            }
        }

        /// <summary>
        /// 导出DataTable到Excel
        /// </summary>
        /// <param name="Code">配置编码</param>
        /// <param name="Data">DataTable数据源</param>
        /// <param name="PageSize">每个Sheet的记录数</param>
        /// <param name="Response">HTTP响应对象</param>
        public static void Export(string Code, DataTable Data, int PageSize, HttpResponse Response)
        {
            if (null == Data || Data.Rows.Count <= 0) { return; }

            //===================获取配置=========================
            CM.Table table = CM.Config.GetXlsExporterConfig(Code);
            table.Columns.Sort((c1, c2) =>
            {
                return c1.Order - c2.Order;
            });
            string strXlsName = table.Name + "-" + DateTime.Now.ToString("yyMMddHHmmss");

            int intMaxPage = 1;
            if (PageSize > 0)
            {            
                intMaxPage = (0 == Data.Rows.Count % PageSize) ? (Data.Rows.Count / PageSize) : (Data.Rows.Count / PageSize + 1);
            }
            //====================================================

            //===================创建表格=========================
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "ZofX";
            hssfworkbook.DocumentSummaryInformation = dsi;
            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = strXlsName;
            hssfworkbook.SummaryInformation = si;            
            //====================================================

            int intNowPage = 1, intIndex = 0;
            for (int intPage = intNowPage; intPage <= intMaxPage; intPage++)
            {
                //===================创建工作表=======================
                //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
                //So we insert three sheet just like what Excel does
                ISheet sheet1 = hssfworkbook.CreateSheet(table.Name + "_" + intPage);
                ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false;
                ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false;
                //====================================================

                //===================创建标题=========================
                IRow row = sheet1.CreateRow(0);
                row.Height = 500;
                ICell cell = row.CreateCell(0, CellType.String);
                cell.SetCellValue(table.Name);
                ICellStyle style = hssfworkbook.CreateCellStyle();
                style.VerticalAlignment = VerticalAlignment.Center;
                style.Alignment = HorizontalAlignment.Center;
                IFont font = hssfworkbook.CreateFont();
                font.Color = HSSFColor.Black.Index;
                font.FontHeight = 250;
                font.Boldweight = 1000;
                style.SetFont(font);
                cell.CellStyle = style;
                sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
                //====================================================

                //===================创建表头=========================
                row = sheet1.CreateRow(1);
                row.Height = 400;
                int rowIndex = 0;
                foreach (CM.Column c in table.Columns)
                {
                    cell = row.CreateCell(rowIndex, CellType.String);
                    cell.SetCellValue(c.Name);

                    style = hssfworkbook.CreateCellStyle();
                    style.BorderBottom = BorderStyle.Thin;
                    style.BottomBorderColor = HSSFColor.Black.Index;
                    style.BorderLeft = BorderStyle.Thin;
                    style.LeftBorderColor = HSSFColor.Black.Index;
                    style.BorderRight = BorderStyle.Thin;
                    style.RightBorderColor = HSSFColor.Black.Index;
                    style.BorderTop = BorderStyle.Thin;
                    style.TopBorderColor = HSSFColor.Black.Index;
                    style.VerticalAlignment = VerticalAlignment.Center;
                    font = hssfworkbook.CreateFont();
                    font.Color = HSSFColor.Black.Index;
                    font.FontHeight = 200;
                    font.Boldweight = 1000;
                    style.SetFont(font);
                    cell.CellStyle = style;

                    sheet1.SetColumnWidth(rowIndex++, 5000);
                }
                //====================================================

                //===================写入数据=========================
                rowIndex = 0;
                if (null != Data)
                {
                    for (; intIndex < Data.Rows.Count && intIndex < PageSize * intPage; intIndex++)
                    {
                        DataRow obj = Data.Rows[intIndex];
                        row = sheet1.CreateRow(2 + rowIndex++);
                        row.Height = 400;

                        int colIndex = 0;
                        foreach (CM.Column c in table.Columns)
                        {
                            try
                            {
                                object val = obj[c.Property];
                                cell = row.CreateCell(colIndex, CellType.String);
                                if (null != val)
                                {
                                    if (val is DateTime)
                                    {
                                        cell.SetCellValue(((DateTime)val).ToString("yyyy-MM-dd HH:mm:ss"));
                                    }
                                    else
                                    {
                                        cell.SetCellValue(val.ToString());
                                    }
                                }
                                else
                                {
                                    cell.SetCellValue("");
                                }
                                style.GetFont(hssfworkbook).Boldweight = 0;
                                cell.CellStyle = style;
                                sheet1.SetColumnWidth(colIndex++, 5000);
                            }
                            catch { }
                        }
                    }
                }
                //====================================================
            }

            if (null != Response)
            {
                Response.ClearHeaders();
                Response.ContentType = "application/vnd.ms-excel";
                Response.Charset = "UTF-8";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", strXlsName + ".xls"));
                Response.Clear();

                MemoryStream file = new MemoryStream();
                hssfworkbook.Write(file);
                Response.BinaryWrite(file.GetBuffer());

                Response.Flush();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Code"></param>
        /// <param name="List"></param>
        /// <param name="PageSize"></param>
        /// <param name="XlsName"></param>
        /// <returns></returns>
        public static Stream Export(string Code, IList List, int PageSize, out string XlsName)
        {
            //===================获取配置=========================
            CM.Table table = CM.Config.GetXlsExporterConfig(Code);
            return Export(table, List, PageSize, out XlsName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="List"></param>
        /// <param name="PageSize"></param>
        /// <param name="XlsName"></param>
        /// <returns></returns>
        public static Stream Export(CM.Table table, IList List, int PageSize, out string XlsName)
        {
            //===================获取配置=========================           
            string strXlsName = table.Name + "-" + DateTime.Now.ToString("yyMMddHHmmss");
            table.Columns.Sort((c1, c2) =>
            {
                return c1.Order - c2.Order;
            });

            int intMaxPage = 1;
            if (PageSize > 0)
            {
                intMaxPage = (0 == List.Count % PageSize) ? (List.Count / PageSize) : (List.Count / PageSize + 1);
            }
            //====================================================

            //===================创建表格=========================
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "ZofX";
            hssfworkbook.DocumentSummaryInformation = dsi;
            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = strXlsName;
            hssfworkbook.SummaryInformation = si;

            int intNowPage = 1, intIndex = 0;
            for (int intPage = intNowPage; intPage <= intMaxPage; intPage++)
            {
                //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
                //So we insert three sheet just like what Excel does
                ISheet sheet1 = hssfworkbook.CreateSheet(table.Name + "_" + intPage);
                ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false;
                ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false;
                //====================================================

                //===================创建标题=========================
                IRow row = sheet1.CreateRow(0);
                row.Height = 500;
                ICell cell = row.CreateCell(0, CellType.String);
                cell.SetCellValue(table.Name);
                ICellStyle style = hssfworkbook.CreateCellStyle();
                style.VerticalAlignment = VerticalAlignment.Center;
                style.Alignment = HorizontalAlignment.Center;
                IFont font = hssfworkbook.CreateFont();
                font.Color = HSSFColor.Black.Index;
                font.FontHeight = 250;
                font.Boldweight = 1000;
                style.SetFont(font);
                cell.CellStyle = style;
                sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
                //====================================================

                //===================创建表头=========================
                row = sheet1.CreateRow(1);
                row.Height = 400;
                int rowIndex = 0;
                foreach (CM.Column c in table.Columns)
                {
                    cell = row.CreateCell(rowIndex, CellType.String);
                    cell.SetCellValue(c.Name);

                    style = hssfworkbook.CreateCellStyle();
                    style.BorderBottom = BorderStyle.Thin;
                    style.BottomBorderColor = HSSFColor.Black.Index;
                    style.BorderLeft = BorderStyle.Thin;
                    style.LeftBorderColor = HSSFColor.Black.Index;
                    style.BorderRight = BorderStyle.Thin;
                    style.RightBorderColor = HSSFColor.Black.Index;
                    style.BorderTop = BorderStyle.Thin;
                    style.TopBorderColor = HSSFColor.Black.Index;
                    style.VerticalAlignment = VerticalAlignment.Center;
                    font = hssfworkbook.CreateFont();
                    font.Color = HSSFColor.Black.Index;
                    font.FontHeight = 200;
                    font.Boldweight = 1000;
                    style.SetFont(font);
                    cell.CellStyle = style;

                    sheet1.SetColumnWidth(rowIndex++, 5000);
                }
                //====================================================

                //===================写入数据=========================
                rowIndex = 0;
                if (null != List)
                {
                    //foreach (object obj in list)
                    for (; intIndex < List.Count && intIndex < PageSize * intPage; intIndex++)
                    {
                        row = sheet1.CreateRow(2 + rowIndex++);
                        row.Height = 400;

                        int colIndex = 0;
                        foreach (CM.Column c in table.Columns)
                        {
                            object val = List[intIndex];
                            Type type = val.GetType();
                            string[] properties = c.Property.Split('.');
                            PropertyInfo pInfo = null;
                            string propertyName = string.Empty;
                            int propertyIndex = -1;
                            foreach (string property in properties)
                            {
                                var m = regexIndexProperty.Match(property);
                                if (m.Success)
                                {
                                    propertyName = m.Groups[1].Value;
                                    propertyIndex = int.Parse(m.Groups[2].Value);
                                }
                                else
                                {
                                    propertyName = property;
                                    propertyIndex = -1;
                                }
                                pInfo = type.GetProperty(propertyName);
                                if (null == pInfo) break;

                                val = pInfo.GetValue(val);
                                if (null == val) break;

                                if (propertyIndex >= 0)
                                {
                                    PropertyInfo indexerProperty = val.GetType().GetProperty("Item");
                                    if (null != indexerProperty)
                                    {
                                        val = indexerProperty.GetValue(val, new object[] { propertyIndex });
                                        if (null == val) break;
                                    }
                                }

                                type = val.GetType();
                                if (null == type) break;
                            }

                            cell = row.CreateCell(colIndex, CellType.String);
                            if (null != val)
                            {
                                if (val is DateTime)
                                {
                                    cell.SetCellValue(((DateTime)val).ToString("yyyy-MM-dd HH:mm:ss"));
                                }
                                else
                                {
                                    cell.SetCellValue(val.ToString());
                                }
                            }
                            else
                            {
                                cell.SetCellValue("");
                            }
                            style.GetFont(hssfworkbook).Boldweight = 0;
                            cell.CellStyle = style;
                            sheet1.SetColumnWidth(colIndex++, 5000);
                        }
                    }
                }
                //====================================================
            }

            XlsName = strXlsName;
            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);
            return file;
        }
    }
}